#!/usr/bin/python
# -*- coding: UTF-8 -*-
import os
import xlrd
import xlwt
import MySQLdb

db = MySQLdb.connect("localhost","root","123456","abm_region")
print(db)

def queryRegionName(name,type):
    '1-省，2-市，3-区'

    if type==1:
        name = name.replace('省','')
        name = name.replace('壮族自治区','')
        name = name.replace('维吾尔自治区','')
        name = name.replace('回族自治区','')
        name = name.replace('自治区','')

    cursor = db.cursor()
    sql = "select * from w_region where type =" + str(type) + " and name = '" + name + "'"
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        if result is None:
            if type ==3:
                '其他区'
                print(name)
                return 22847
            print(sql)
        return result[0]
    except:
        print('error')

class PreOrder:
    '原始订单'
    product_id = 0
    product_count = 0
    receiver_name = ''
    receiver_id_name = ''
    receiver_id_no = ''
    receiver_mobile = ''
    receiver_province = ''
    receiver_city = ''
    receiver_district = ''
    receiver_address = ''

    agency_mobile = ''

    def __init__(self, excel_row):
        self.product_id = int(excel_row[2])
        self.product_count = int(excel_row[3])
        self.receiver_name = excel_row[5]
        self.receiver_id_name = excel_row[6]
        self.receiver_id_no = excel_row[7]
        self.receiver_mobile = excel_row[8]
        self.receiver_province = excel_row[9]
        self.receiver_city = excel_row[10]
        self.receiver_district = excel_row[11]
        self.receiver_address = excel_row[12]
        self.agency_mobile = excel_row[14]

class ResultOrder:
    agency_mobile = ''
    product_id = 0
    product_count = 0
    receiver_name = ''
    receiver_id_name = ''
    receiver_id = ''
    receiver_mobile =''
    receiver_province = 0
    receiver_city = 0
    receiver_district = 0
    receiver_address = ''

    def __init__(self, preOrder:PreOrder):
        self.product_id = preOrder.product_id
        self.product_count = preOrder.product_count
        self.agency_mobile = preOrder.agency_mobile
        self.receiver_name = preOrder.receiver_name
        self.receiver_id_name = preOrder.receiver_id_name
        self.receiver_id = preOrder.receiver_id_no
        self.receiver_mobile = preOrder.receiver_mobile


        self.receiver_address = preOrder.receiver_address.replace(preOrder.receiver_province,'')
        self.receiver_address = self.receiver_address.replace(preOrder.receiver_city,'')
        self.receiver_address = self.receiver_address.replace(preOrder.receiver_district,'')

        self.receiver_province = queryRegionName(preOrder.receiver_province, 1)
        self.receiver_city = queryRegionName(preOrder.receiver_city, 2)
        self.receiver_district = queryRegionName(preOrder.receiver_district, 3)

def read_order_file(filename):
    print('读取文件:' + filename)
    orders = []
    xlrd.Book.encoding = "utf8" #设置编码
    data = xlrd.open_workbook(filename)
    table = data.sheet_by_index(0) #取第一张工作簿

    for rownum in range(table.nrows):
        if rownum == 0:
            continue
        row = table.row_values(rownum)
        o = PreOrder(row)
        orders.append(o)
    
    return orders

def write_result_file(orders, filename):
    data=xlwt.Workbook()
    table=data.add_sheet('order')

    resultOrders = []
    for i in range(len(orders)):
        o = ResultOrder(orders[i])
        resultOrders.append(o)

    for rownum in range(len(resultOrders)):
        o = resultOrders[rownum]
        table.write(rownum+1,3,o.agency_mobile)
        table.write(rownum+1,4,o.product_id)
        table.write(rownum+1,5,o.product_count)
        table.write(rownum+1,6,o.receiver_name)
        table.write(rownum+1,7,o.receiver_id_name)
        table.write(rownum+1,8,o.receiver_id)
        table.write(rownum+1,9,o.receiver_mobile)
        table.write(rownum+1,10,o.receiver_province)
        table.write(rownum+1,11,o.receiver_city)
        table.write(rownum+1,12,o.receiver_district)
        table.write(rownum+1,13,o.receiver_address)

    data.save(filename)
    return

def process_file(filename):
    name = os.path.splitext(file)[0]

    orders = read_order_file(filename)
    result_filename = '_' + name + '.xls'
    write_result_file(orders, result_filename)
    return


curdir = os.path.abspath(os.curdir)
files= os.listdir(curdir)
for file in files:
    if(file[0]=='.' or file[0]=='~'):
        continue

    ext = os.path.splitext(file)[1]
    if ext == '.xlsx' or ext == 'xls':
        process_file(file)

    
db.close()